package com.fs.ibox_h.dao.impl;


import com.fs.ibox_h.dao.IdCollectionDao;
import com.fs.ibox_h.pojo.Collection;
import com.fs.ibox_h.pojo.IdCollection;
import com.fs.ibox_h.util.DateUtil;
import com.fs.ibox_h.util.JDBCUtil;
import com.fs.ibox_h.vo.CollectionCondition;
import com.fs.ibox_h.vo.IdCollectionCondition;
import com.fs.ibox_h.vo.Position;
import com.fs.ibox_h.vo.StatsCname;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Author:HOU
 * date:2022/11/15  15:31
 * 案例:
 **/
public class IdCollectionDaoimpl implements IdCollectionDao {

    @Override
    public List<Position> selectByCname(String cname) {
        String sql="SELECT username , COUNT(1) num  FROM tb_idcollection WHERE cname=? group by username";
        List<Position> positions = JDBCUtil.executeQuery(sql, Position.class, cname);
        return positions;
    }

    @Override
    public List<IdCollection> selectAll() {
        String sql="SELECT * FROM tb_idcollection";
        List<IdCollection> idCollections = JDBCUtil.executeQuery(sql, IdCollection.class);
        return idCollections;
    }

    @Override
    public List<IdCollection> selectByCnameStateId(int currentPageInt, int pageSizeInt, IdCollectionCondition idCollectionCondition) {
        StringBuffer  sql =new StringBuffer("SELECT * FROM tb_idcollection where 1=1 ");
        List<Object> paramList = new ArrayList<>();
        appedSql(sql,paramList,idCollectionCondition);
        sql.append(" limit ? , ?");
        paramList.add((currentPageInt-1)*pageSizeInt);
        paramList.add(pageSizeInt);
        List<IdCollection> idcollections = JDBCUtil.executeQuery(sql.toString(), IdCollection.class, paramList.toArray());
        return idcollections;

    }

    @Override
    public int count(IdCollectionCondition idCollectionCondition) {
        //String 不可变字符串
        //StringBuffer(线程安全) StringBuilder(线程不安全)
        StringBuffer sql = new StringBuffer(" SELECT COUNT(1) FROM tb_idcollection WHERE 1=1 ");
        //给?赋的值  值的顺序与?顺序一致
        List<Object> paramList = new ArrayList<>();
        //拼接sql
        appedSql(sql,paramList,idCollectionCondition);

        Connection conn = null;
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            conn = JDBCUtil.getConnection();
            psmt = conn.prepareStatement(sql.toString());
            //给?赋值
            for (int i = 0; i < paramList.size(); i++) {
                psmt.setObject(i+1,paramList.get(i));
            }
            rs = psmt.executeQuery();
            rs.next();
            return rs.getInt(1);
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtil.close(rs,psmt,conn);
        }
        return 0;

    }

    @Override
    public List<StatsCname> selectAllCnaem() {
        String sql="SELECT cname , COUNT(1) num  FROM tb_idcollection  group by cname";
        List<StatsCname> statsCnames = JDBCUtil.executeQuery(sql, StatsCname.class);
        return statsCnames;
    }

    private void appedSql(StringBuffer sql, List<Object> paramList, IdCollectionCondition idCollectionCondition){
        if (idCollectionCondition!=null){
            if(idCollectionCondition.getName()!=null&&!idCollectionCondition.getName().isEmpty()){
                sql.append(" AND cname like ? ");
                paramList.add("%"+idCollectionCondition.getName()+"%");
            }
            if (idCollectionCondition.getStaid()!=null){
                sql.append(" AND id>=?");
                paramList.add(idCollectionCondition.getStaid());
            }
             if (idCollectionCondition.getEndid()!=null){
                sql.append(" AND id<=?");
                paramList.add(idCollectionCondition.getEndid());
            }
            if(idCollectionCondition.getCstate()!=null){
               sql.append(" AND cstate=?");
               paramList.add(idCollectionCondition.getCstate());
            }
        }
    }
}
